/*
 * Copyright (C) Francisco Morero Peyrona. All rights reserved.
 *
 * This software is published under the terms of Open Source
 * License version 1.1, a copy of which has been included with this
 * distribution in the License.txt file.
 */

package jdbcmanager.dbtools;

import java.sql.*;
import javax.swing.*;

import jdbcmanager.app.*;
import jdbcmanager.dbtools.agentwise.*;
import jdbcmanager.dbtools.datatypes.*;
import jdbcmanager.dbtools.tableditor.*;

/**
 * Methods here are all static and are dedicated to achive 2 kind of tasks:
 * <ul>
 *    <li>Common point for GUI tasks (i.e. creating a table dialog)
 *    <li>Minor tasks like names normalizations, and so.
 * </ul>
 * <p>
 * These methods deal with the selected AgentSQL (or default one) to send the
 * information (tables, indices, ... defintions) and is resposability of the
 * AgentSQL to create appropriate SQL command(s) and execute them. To execute
 * them there is a convenience method in this class: <code>execute(...)</code> 
 * 
 * @author Francisco Morero Peyrona
 */
public class SqlUtil   // Can't be final because some AgentMetaData or AgentSQL could be interested in extending it
{
   //-------------------------------------------------------------------------//
   // CATALOG RELATED METHODS
   //-------------------------------------------------------------------------//
   
   /**
    * 
    * @param conn
    * @return <code>true</code> if everithing goes fine, otherwise <code>false</code>
    */
   public static boolean createCatalog( Connection conn )
   {
      boolean bSuccess = false;
      
      if( DbUtil.getAgentDBMS().getAgentSQL().allowsCatalogCreation() )
      {
         AgentMetaData metadata = DbUtil.getAgentDBMS().getAgentMetaData();
         String        sCatTerm = metadata.getCatalogTerm();
         
         String  sCatalog = App.getStringUsingDialog( "Create "+ sCatTerm,
                                                      "Enter new "+ sCatTerm +" name", "", false );
         if( sCatalog != null )
         {
            sCatalog = DbUtil.getAgentDBMS().getAgentSQL().normalizeDbEntityName( sCatalog, DbEntityTypes.ENTITY_CATALOG );
            
            if( sCatalog.length() > 0 )
            {
               if( App.indexOf( metadata.getCatalogs(), sCatalog, false ) > -1 )
                  App.showMessage( "Can not create "+ sCatTerm, "\""+ sCatalog +"\" Name already exists" );  
               else
                  bSuccess = DbUtil.getAgentDBMS().getAgentSQL().createCatalog( conn, sCatalog );
            }
         }
      }
      else
      {
         String sCatalogTerm = DbUtil.getAgentDBMS().getAgentMetaData().getCatalogTerm(); 
          
         App.showMessage( "Can't create "+ sCatalogTerm, "The DBMS that you are using does not allows "+ sCatalogTerm +" creation." );
      }
      
      return bSuccess;
   }
   
   /**
    * 
    * @param conn
    * @param sCatalog
    * @return <code>true</code> if everithing goes fine, otherwise <code>false</code>
    */
   public static boolean dropCatalog( Connection conn, String sCatalog )
   {
      boolean bSuccess = false;
      String  sCatTerm = DbUtil.getAgentDBMS().getAgentMetaData().getCatalogTerm();
      
      if( DbUtil.getAgentDBMS().getAgentSQL().allowsCatalogCreation() )
      {
         if( App.confirmDialog( "Deleting "+ sCatTerm, "Are you sure you want to delete "+ sCatTerm +" \""+ sCatalog +"\"?" ) )
            bSuccess = DbUtil.getAgentDBMS().getAgentSQL().dropCatalog( conn, sCatalog );
      }
      else    
         App.showMessage( "Can't destroy "+sCatTerm, "The DBMS that you are using does not allows "+sCatTerm +" destruction." );

      return bSuccess;
   }
   
   /**
    * 
    * @param conn
    * @param sOldName
    * @return <code>true</code> if everithing goes fine, otherwise <code>false</code>
    */
   public static boolean renameCatalog( Connection conn, String sOldName )
   {
      boolean bSuccess = false;
      String  sCatTerm = DbUtil.getAgentDBMS().getAgentMetaData().getCatalogTerm();
      
      if( DbUtil.getAgentDBMS().getAgentSQL().allowsCatalogRename() )
      {
         String sNewName = App.getStringUsingDialog( "Rename "+ sCatTerm +" \""+ sOldName +"\"",
                                                     "Enter new "+ sCatTerm +" name", "", false );
         if( sNewName != null )
            bSuccess = renameCatalog( conn, sOldName, sNewName );
      }
      else
      {
         App.showMessage( "Can't rename "+sCatTerm, "The DBMS that you are using does not allow to rename "+ sCatTerm +"s." );
      }
      
      return bSuccess;
   }
   
   /**
    * 
    * @param conn
    * @param sOldName
    * @param sNewName
    * @return
    */
   public static boolean renameCatalog( Connection conn, String sOldName, String sNewName )
   {
      boolean bSuccess = false;
      String  sCatTerm = DbUtil.getAgentDBMS().getAgentMetaData().getCatalogTerm();
      
      if( DbUtil.getAgentDBMS().getAgentSQL().allowsCatalogRename() )
      {
         sNewName = DbUtil.getAgentDBMS().getAgentSQL().normalizeDbEntityName( sNewName, DbEntityTypes.ENTITY_TABLE );

         if( sNewName.length() > 0 )
         {
            if( App.indexOf( DbUtil.getAgentDBMS().getAgentMetaData().getCatalogs(), sNewName, false ) > -1 )
               App.showMessage( "Can not rename "+ sCatTerm, "\""+ sNewName +"\" Name already exists" );  
            else
               bSuccess = renameCatalog( conn, sOldName, sNewName );
         }
      }  
      else
      {
         App.showMessage( "Can't rename "+sCatTerm, "The DBMS that you are using does not allow to rename "+ sCatTerm +"s." );
      }
      
      return bSuccess;
   }
   
   //-------------------------------------------------------------------------//
   // TABLES RELATED METHODS
   //-------------------------------------------------------------------------//
   
   /**
    * Open a dialog where the user can provides all needed information to create a table, its indices and relations.
    *
    * @param conn
    * @param dedTable
    * @return An instance of <code>TableDefinition</code> if everything goes well or <code>null</code> otherwise.
    */
   public static TableDefinition createTable( Connection conn, DbEntityDescriptor dedTable )
   {
      boolean bSuccess = false;
      
      App.setCursor( java.awt.Cursor.WAIT_CURSOR );
      
      JTableEditorDlg editor = new JTableEditorDlg( new TableDefinition( dedTable ), true );
                      editor.setVisible( true );
      
      App.restoreCursor();
      
      if( editor.getCloseOption() == JOptionPane.OK_OPTION )
         bSuccess = createTable( conn, editor.getTableDefinition() );
      
      return (bSuccess) ? editor.getTableDefinition() : null;      
   }

   /**
    * Creates a table, its indices and relations, based on passed information.
    * 
    * @param conn
    * @param table
    * @return 
    */
   public static boolean createTable( Connection conn, TableDefinition table )
   {
      return DbUtil.getAgentDBMS().getAgentSQL().createTable( conn, table );
   }

   /**
    * Open a dialog where the user change table structure information as well as its indices and relations.
    * 
    * @param conn
    * @param table
    * @return An instance of <code>TableDefinition</code> if everything goes well or <code>null</code> otherwise.
    */ 
   public static TableDefinition alterTable( Connection conn, TableDefinition table )
   {
      boolean bSuccess = false;
      
      App.setCursor( java.awt.Cursor.WAIT_CURSOR );
      
      TableDefinition tableNew = (TableDefinition) table.clone();
      
      JTableEditorDlg editor = new JTableEditorDlg( tableNew, false );
                      editor.setVisible( true );
      
      App.restoreCursor();
      
      if( editor.getCloseOption() == JOptionPane.OK_OPTION )                  // Can't use tableNew
         bSuccess = DbUtil.getAgentDBMS().getAgentSQL().alterTable( conn, table, editor.getTableDefinition() );

      return (bSuccess) ? editor.getTableDefinition() : null;
   }
   
   /**
    * Open a dialog asking to the user, and after confirmation, table is renamed.
    * 
    * @param conn
    * @param ded
    * @param sNewName
    * @return
    */
   public static boolean renameTable( Connection conn, TableDefinition table, String sNewName )
   {
      if( App.confirmDialog( "Please confirm",
                             "Table '"+ table.getName() +"' will be renamed to '"+ sNewName +"'\n"+
                             "Are you sure you want to proceed?" ) )
      {
         return DbUtil.getAgentDBMS().getAgentSQL().renameTable( conn, table, sNewName );
      }

      return false;
   }
   
   /**
    * Open a dialog asking to the user, and after confirmation, table is dropped.
    *
    * @param conn
    * @param sTableName
    * @return <code>true</code> if succeded, or <code>false</code> if failed
    */
   public static boolean dropTable( Connection conn, DbEntityDescriptor ded )
   {
      if( App.confirmDialog( "Please confirm",
                             "Table '"+ ded.getQualifiedName() +"' will be removed (dropped) from "+ 
                             DbUtil.getAgentDBMS().getAgentMetaData().getCatalogTerm() +".\n"+
                             "Are you sure you want to proceed?" ) )
      {
         return DbUtil.getAgentDBMS().getAgentSQL().dropTable( conn, ded );
      }
      
      return false;
   }
   
   //-------------------------------------------------------------------------//
   // OTHER METHODS
   //-------------------------------------------------------------------------//

   /**
    * Convenience method that executes passed SQL command and shows the exception if happen.
    *   
    * @param conn A connection
    * @param sSQL A SQL statement
    * @return <code>true</code> if everything goes fine, otherwise <code>false</code>
    */   
   public static boolean execute( Connection conn, String sSQL )
   {      
      try
      {
         App.setCursor( java.awt.Cursor.WAIT_CURSOR );
         
         Statement stmt = conn.createStatement();
         DbUtil.executeSQL( stmt, sSQL, true );
         
         return true;
      }
      catch( InterruptedException exc )
      {
      	return false;
      }
      catch( SQLException exc )
      {
         App.showException( exc, "Error executing SQL command" );
         return false;
      }
      finally
      {
         App.restoreCursor();
      }
   }
}